var express = require("express");
const { connection } = require("../model/db"); // 获取连接实例
const router = express.Router();

// 示例
// router.get("/msglist", (req, res) => {
//     connection.query(`select * from user;`, (err, data) => {
//         if (err) {
//             console.log("err", err)
//             res.send('query error')
//         } else {
//             // 将 MySQL 查询结果作为路由返回值
//             res.send({
//                 code: 200,
//                 msg: "获取数据成功",
//                 data
//             })
//         }
//     })
// })

// 面试题单条数据添加
router.post("/addSubject", (req, res) => {
  connection.query(`insert into question_bank(write_name,scene) value('${req.body.write_name}',${req.body.scene})`, (err, data) => {
    if (err) {
      console.log("err", err)
      res.send('query error')
    } else {
      // 将 MySQL 查询结果作为路由返回值
      res.send({
        code: 200,
        msg: "添加成功",
      })
    }
  })
})

// 批量添加面试题接口batchSubject
router.post("/batchSubject", (req, res) => {
  let sql = ''
  req.body.msg.forEach((item) => {
    let deptName
    switch (item.dept_name) {
      case "人工智能学院" || "人工" || "人工智能":
        deptName = 1
        break;
      case "传媒学院" || "传媒":
        deptName = 2
        break;
      case "网站工程学院" || "网站" || "网站工程":
        deptName = 3
        break;
      case "云计算学院" || "云计算":
        deptName = 4
        break;
      case "计算机科学与技术学院" || "技科" || "专基":
        deptName = 5
        break;
      default:
        break;
    }
    sql += `('${item.stu_name}','${item.card_id}','${item.classroom}','${item.classname}','${item.seat}',${deptName}),`;
  })
  connection.query(
    `insert into student(stu_name,card_id,classroom,classname,seat,college_id) values ${sql.slice(
      0,
      -1
    )};`,
    (err, data) => {
      if (err) {
        console.log(`err`, err);
        res.send("query error");
      } else {
        // 将 MySQL 查询结果作为路由返回值
        res.send({
          code: 200,
          msg: "添加成功",
        });
      }
    }
  );
})

// 查询所有学生信息
router.get("/getStu", (req, res) => {
  const msg = req.query
  // 分页
  let pageSize = req.query.pageSize || 10
  let pageNum = req.query.pageNum || 1
  let a = (pageNum * 1 - 1) * pageSize * 1
  let sql = 'select * from student'
  let deptName = msg.dept
  if (msg.dept && !msg.card_id && !msg.stu_name) {
    sql = `select a.*,b.dept_name from student as a INNER JOIN department as b on a.college_id = b.id where college_id=${deptName}`
  } else if (msg.dept && msg.card_id && !msg.stu_name) {
    sql = `select a.*,b.dept_name from student as a INNER JOIN department as b on a.college_id = b.id where college_id=${deptName} and card_id=${msg.card_id}`
  } else if (msg.dept && !msg.card_id && msg.stu_name) {
    sql = `select a.*,b.dept_name from student as a INNER JOIN department as b on a.college_id = b.id where college_id=${deptName} and stu_name like '%${msg.stu_name}%'`
  } else if (msg.dept && msg.card_id && msg.stu_name) {
    sql = `select a.*,b.dept_name from student as a INNER JOIN department as b on a.college_id = b.id where college_id=${deptName} and card_id=${msg.card_id} and stu_name like '%${msg.stu_name}%'`
  }
  connection.query(sql, (err, data1) => {
    if (err) {
      res.send({
        code: 400,
        msg: "query error",
        err
      });
    } else {
      // 将 MySQL 查询结果作为路由返回值
      let total = data1.length
      sql = sql + ` LIMIT ${a},${pageSize * 1}`
      connection.query(sql, (err, data) => {
        if (err) {
          res.send({
            code: 400,
            msg: "query error",
            err
          });
        } else {
          // 将 MySQL 查询结果作为路由返回值
          res.send({
            code: 200,
            msg: "获取数据成功",
            data,
            total
          })
        }
      })
    }
  })
})

// 题库查询接口
router.get("/question", (req, res) => {
  let sql = ''
  // 分页
  let pageSize = req.query.pageSize || 10
  let pageNum = req.query.pageNum || 1
  let a = (pageNum * 1 - 1) * pageSize * 1
  if (req.query.id) {
    sql = `select * from question_bank where dept_id = ${req.query.id}`
  } else {
    sql = `select * from question_bank`
  }
  connection.query(sql, (err, data1) => {
    if (err) {
      res.send({
        code: 400,
        msg: "query error",
        err
      });
    } else {
      // 将 MySQL 查询结果作为路由返回值
      let total = data1.length
      sql = sql + ` LIMIT ${a},${pageSize * 1}`
      connection.query(sql, (err, data) => {
        if (err) {
          res.send({
            code: 400,
            msg: "query error",
            err
          });
        } else {
          // 将 MySQL 查询结果作为路由返回值
          res.send({
            code: 200,
            msg: "获取数据成功",
            data,
            total
          })
        }
      })
    }
  })
})

// 删除单条面试题
router.get("/dropQuestion", (req, res) => {
  connection.query(`DELETE FROM question_bank WHERE id = ${req.query.id};`, (err, data) => {
    if (err) {
      console.log("err", err)
      res.send('query error')
    } else {
      // 将 MySQL 查询结果作为路由返回值
      res.send({
        code: 200,
        msg: "删除数据成功",
      })
    }
  })
})

// 删除多条面试题
router.post("/deleteQuestion", (req, res) => {
  const ids = `(${req.body.ids.join()})`
  connection.query(`DELETE FROM question_bank WHERE id in ${ids};`, (err, data) => {
    if (err) {
      console.log("err", err)
      res.send('query error')
    } else {
      // 将 MySQL 查询结果作为路由返回值
      res.send({
        code: 200,
        msg: "删除成功",
      })
    }
  })
})

// 批量添加面试题
router.post("/questionArr", (req, res) => {
  let sql = ''
  req.body.msg.forEach(item => {
    sql += `('${item.write_name}',${item.scene},${item.dept_name}),`
  })
  connection.query(`insert into question_bank(write_name,scene,dept_id) values ${sql.slice(0, -1)};`, (err, data) => {
    if (err) {
      res.send('query error')
    } else {
      // 将 MySQL 查询结果作为路由返回值
      res.send({
        code: 200,
        msg: "添加成功",
      })
    }
  })
})

module.exports = router;
